import psycopg2
import pandas as pd

postgres_host = "172.16.137.39"
postgres_port = "5432"
postgres_user = "a2513220218"
postgres_password = "xawl-6043"
postgres_database = "gp101"

conn_string = "host=" + postgres_host + " port=" + postgres_port + " dbname=" + postgres_database + " user=" + postgres_user + " password=" + postgres_password
conn = psycopg2.connect(conn_string)
cursor = conn.cursor()

csv_file_path = "D:\数据库\实验报告\附件1--car_price_sample_1k\car_price_sample_1k.csv"
df = pd.read_csv(csv_file_path)
column_names = list(df.columns)

create_table_sql = "CREATE TABLE car_sales_1 ({})"
column_definitions = []
for col in column_names:
    if df[col].dtype == 'object':
        column_definitions.append(f"{col} VARCHAR(255)")
    elif df[col].dtype == 'int64':
        column_definitions.append(f"{col} BIGINT")
    elif df[col].dtype == 'float64':
        column_definitions.append(f"{col} DECIMAL(10, 2)")
    elif df[col].dtype == 'datetime64':
        column_definitions.append(f"{col} TIMESTAMP")
create_table_sql = create_table_sql.format(", ".join(column_definitions))

try:
    # 执行创建表的SQL语句
    cursor.execute(create_table_sql)
    # 提交事务，使表创建操作生效
    conn.commit()
    print("数据表car_sales_1创建成功")

    # 将CSV文件数据插入到新创建的表中
    insert_sql = "INSERT INTO car_sales_1 ({}) VALUES ({})"
    columns = ", ".join(column_names)
    values_template = ", ".join(["%s"] * len(column_names))
    insert_sql = insert_sql.format(columns, values_template)

    for index, row in df.iterrows():
        cursor.execute(insert_sql, tuple(row))

    # 提交事务，使插入操作生效
    conn.commit()
    print("CSV文件数据成功导入到car_sales_1表中")
except (psycopg2.DatabaseError, psycopg2.OperationalError) as e:
    print(f"创建数据表或导入数据时出错: {e}")
    conn.rollback()

cursor.close()
conn.close()